package dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import bean.JavaBean;

public class TeacherDao {
	
	private final static String driverName = "com.mysql.cj.jdbc.Driver";
	private final static String url = "jdbc:mysql://localhost:3306/dbjsp?"
			+ "user=root&password=root&useUnicode=true&characterEncoding=UTF-8"
			+ "&serverTimezone=UTC&useSSL=false";
	
	public static List<JavaBean> readOneList3(String str1,String str2) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs=null;
		List<JavaBean> list=new ArrayList<JavaBean>();
		
		try {
			Class.forName(driverName);
			conn = DriverManager.getConnection(url);
			String sql="select * from teacher where "+ str1 +" = ?";
			ps = conn.prepareStatement(sql);
			/*String r = "\'";
			str1=str1.replaceAll(r, "");*/
			ps.setString(1, str2);//会自动加单引号
			rs = ps.executeQuery();
			/*System.out.println(ps.toString());*/
			
			while(rs.next()) {
				String id=rs.getString("id");
				String name=rs.getString("name");
				String age=rs.getString("age");
				String title=rs.getString("title");
				String department=rs.getString("department");
				String course=rs.getString("course");
				String phone=rs.getString("phone");
				String address=rs.getString("address");
				JavaBean dg=new JavaBean(id, name, age, title,department,course,phone,address);
				list.add(dg);		
			}
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			try {
			if(rs!=null) {
				rs.close();
			}
			if(ps!=null) {
				ps.close();
			}
			if(conn!=null) {
				conn.close();
				}
			}catch(SQLException e) {
				e.printStackTrace();
			}
			
		}
		return list;
	}
	
	public static List<JavaBean> readOneList2(String str1,String str2) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs=null;
		List<JavaBean> list=new ArrayList<JavaBean>();
		
		try {
			Class.forName(driverName);
			conn = DriverManager.getConnection(url);
			String sql="select * from teacher where "+ str1 +" like ?";
			ps = conn.prepareStatement(sql);
			/*String r = "\'";
			str1=str1.replaceAll(r, "");*/
			ps.setString(1, "%"+str2+"%");//会自动加单引号
			rs = ps.executeQuery();
			/*System.out.println(ps.toString());*/
			
			while(rs.next()) {
				String id=rs.getString("id");
				String name=rs.getString("name");
				String age=rs.getString("age");
				String title=rs.getString("title");
				String department=rs.getString("department");
				String course=rs.getString("course");
				String phone=rs.getString("phone");
				String address=rs.getString("address");
				JavaBean dg=new JavaBean(id, name, age, title,department,course,phone,address);
				list.add(dg);		
			}
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			try {
			if(rs!=null) {
				rs.close();
			}
			if(ps!=null) {
				ps.close();
			}
			if(conn!=null) {
				conn.close();
				}
			}catch(SQLException e) {
				e.printStackTrace();
			}
			
		}
		return list;
	}
	//判断教师id是否存在
	public static boolean checkId(String id) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs=null;
		boolean ifId=false;
		try {
			
			Class.forName(driverName);
			conn = DriverManager.getConnection(url);
			ps = conn.prepareStatement("select * from teacher where id=?");
			ps.setString(1, id);
			rs = ps.executeQuery();	
			if(rs.next()) ifId=true;
			
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			try {
			if(rs!=null) {
				rs.close();
			}
			if(ps!=null) {
				ps.close();
			}
			if(conn!=null) {
				conn.close();
				}
			}catch(SQLException e) {
				e.printStackTrace();
			}
			
		}
		return ifId;
	}

	//注册
	public static void regist(String u,String p) {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			Class.forName(driverName);
			conn = DriverManager.getConnection(url);
			ps = conn.prepareStatement("insert into user values(?,?)");
			ps.setString(1, u);
			ps.setString(2, p);
			ps.executeUpdate();
				
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (ps != null) {
					ps.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (Exception e2) {
				e2.printStackTrace();
			}
		}
	}
	//判断注册id是否存在
	public static boolean checkRegister(String username) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs=null;
		boolean ifRegister=false;
	
		
		try {
			
			Class.forName(driverName);
			conn = DriverManager.getConnection(url);
			ps = conn.prepareStatement("select * from user where username=?");
			ps.setString(1, username);
			rs = ps.executeQuery();	
			if(rs.next()) ifRegister=true;
			
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			try {
			if(rs!=null) {
				rs.close();
			}
			if(ps!=null) {
				ps.close();
			}
			if(conn!=null) {
				conn.close();
				}
			}catch(SQLException e) {
				e.printStackTrace();
			}
			
		}
		return ifRegister;
	
	}
	
	//登录
	public static boolean checkLogin(String username,String password) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs=null;
		boolean ifLogin=false;
	
		
		try {
			
			Class.forName(driverName);
			conn = DriverManager.getConnection(url);
			ps = conn.prepareStatement("select * from user where username=? and password=?");
			ps.setString(1, username);
			ps.setString(2, password);
			rs = ps.executeQuery();	
			if(rs.next()) ifLogin=true;
			
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			try {
			if(rs!=null) {
				rs.close();
			}
			if(ps!=null) {
				ps.close();
			}
			if(conn!=null) {
				conn.close();
				}
			}catch(SQLException e) {
				e.printStackTrace();
			}
			
		}
		return ifLogin;
	
	}
	
	
	
	public static List<JavaBean> readOneList(String i) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs=null;
		List<JavaBean> list=new ArrayList<JavaBean>();
		
		try {
			Class.forName(driverName);
			conn = DriverManager.getConnection(url);
			ps = conn.prepareStatement("select * from teacher where id=?");
			ps.setString(1, i);
			rs = ps.executeQuery();
			/*System.out.println(ps);*/
			while(rs.next()) {
				String id=rs.getString("id");
				String name=rs.getString("name");
				String age=rs.getString("age");
				String title=rs.getString("title");
				String department=rs.getString("department");
				String course=rs.getString("course");
				String phone=rs.getString("phone");
				String address=rs.getString("address");
				JavaBean dg=new JavaBean(id, name, age, title,department,course,phone,address);
				list.add(dg);		
			}
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			try {
			if(rs!=null) {
				rs.close();
			}
			if(ps!=null) {
				ps.close();
			}
			if(conn!=null) {
				conn.close();
				}
			}catch(SQLException e) {
				e.printStackTrace();
			}
			
		}
		return list;
	}
	//读取
	public static List<JavaBean> readList() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs=null;
		List<JavaBean> list=new ArrayList<JavaBean>();
		
		try {
			Class.forName(driverName);
			conn = DriverManager.getConnection(url);
			ps = conn.prepareStatement("select * from teacher");
			rs = ps.executeQuery();
			
			while(rs.next()) {
				String id=rs.getString("id");
				String name=rs.getString("name");
				String age=rs.getString("age");
				String title=rs.getString("title");
				String department=rs.getString("department");
				String course=rs.getString("course");
				String phone=rs.getString("phone");
				String address=rs.getString("address");
				JavaBean dg=new JavaBean(id, name, age, title,department,course,phone,address);
				list.add(dg);		
			}
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			try {
			if(rs!=null) {
				rs.close();
			}
			if(ps!=null) {
				ps.close();
			}
			if(conn!=null) {
				conn.close();
				}
			}catch(SQLException e) {
				e.printStackTrace();
			}
			
		}
		return list;
	}
	
	// 添加
	public static void insert(String id, String name, String age, String title, String department, String course, String phone,
			String address) {
		Connection conn = null;
		PreparedStatement ps = null;
		
		try {
			Class.forName(driverName);
			conn = DriverManager.getConnection(url);
			ps = conn.prepareStatement("insert into teacher values(?,?,?,?,?,?,?,?)");
			ps.setString(1, id);
			ps.setString(2, name);
			ps.setString(3, age);
			ps.setString(4, title);
			ps.setString(5, department);
			ps.setString(6, course);
			ps.setString(7, phone);
			ps.setString(8, address);
			ps.executeUpdate();
				
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (ps != null) {
					ps.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (Exception e2) {
				e2.printStackTrace();
			}
		}
	}
	
	//修改
	public static void update(String id, String name, String age, String title, String department, String course, String phone,
			String address) {
		Connection conn = null;
		PreparedStatement ps = null;
		
		try {
			Class.forName(driverName);
			conn = DriverManager.getConnection(url);
			ps = conn.prepareStatement("update teacher set name=?,age=?,title=?,department=?,course=?,phone=?,address=?  where id=?");
			ps.setString(1, name);
			ps.setString(2, age);
			ps.setString(3, title);
			ps.setString(4, department);
			ps.setString(5, course);
			ps.setString(6, phone);
			ps.setString(7, address);
			ps.setString(8, id);
			ps.executeUpdate();
				
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (ps != null) {
					ps.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (Exception e2) {
				e2.printStackTrace();
			}
		}
	}
	
	//删除
	public static void delete(String id){
		Connection conn = null;
		PreparedStatement ps = null;
		
		try {
			Class.forName(driverName);
			conn = DriverManager.getConnection(url);
			ps = conn.prepareStatement("delete from teacher where id=?");
			ps.setString(1, id);
			ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (ps != null) {
					ps.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (Exception e2) {
				e2.printStackTrace();
			}
		}
	}
}
	
